package com.asdan.seededu.front.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.asdan.seededu.entity.security.SubProject;
import com.asdan.seededu.entity.security.User;
import com.asdan.seededu.front.service.UserInformationService;
import com.asdan.seededu.service.impl.UtilImpl;
import com.asdan.seededu.util.DatabaseAccess;
import com.asdan.seededu.util.Pager;
import com.asdan.seededu.util.Util;
import com.demo2do.core.utils.JsonUtils;


public class UserInfoServiceImp extends UtilImpl implements UserInformationService  {

	public List<SubProject> userProInfo(int uid) {
		
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		SubProject subpro=new SubProject();
		List<SubProject> list=new ArrayList<SubProject>();
 		String sql="select FPID, SUBPROJECTNAME,PCREATTIME,STARTTIME,description,SPID from  v_user_school_project_subproject where uid=? ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, uid) ;
		    rs=pstmt.executeQuery();
			while(rs.next()){
				if(rs.getInt("SPID")!=0)
				{
				subpro=new SubProject();
				subpro.setFpid(rs.getInt("FPID"));
				subpro.setName(rs.getString("SUBPROJECTNAME"));
				subpro.setPcreatTime(rs.getString("PCREATTIME"));
				subpro.setStartTime(rs.getString("STARTTIME"));
				subpro.setProjectinformation(rs.getString("description"));
				subpro.setSpid(rs.getInt("SPID"));
				list.add(subpro);
				}
				
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					
			}
		}
		return list;
	}

	public int getWYEFid() {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		int fpid=0;
		
 		String sql="select fpid from project where name='WYEF'";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			if(rs.next()){
				fpid=rs.getInt("fpid");
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					
			}
		}
		
		return fpid;
	}
	
	public int getISWEEPId() {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		int isweepfpid=0;
		
 		String sql="select fpid from project where name='ISWEEP'";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			if(rs.next()){
				isweepfpid=rs.getInt("fpid");
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					
			}
		}
		
		return isweepfpid;
	}
	
	public boolean updateUserInfo(User user) {
		boolean temp = true ;
		
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		String sql = "update user  set  name=? ,grade=? ,email=? ,cellphone=?,qq=?,idcard=?,position=?," +
				"passid=? where uid= ?" ;
		try {
			cn = databaseAccess.getConn() ;
			pstmt = cn.prepareStatement(sql) ;
			pstmt.setString(1,user.getName()) ;
			pstmt.setString( 2, user.getGrade()) ;
			pstmt.setString(3, user.getEmail()) ;
			pstmt.setString(4, user.getCellPhone()) ;
			pstmt.setString( 5, user.getQQ()) ;
			pstmt.setString(6,user.getIdCard()) ;
			pstmt.setString( 7, user.getPosition()) ;
			pstmt.setString(8, user.getPassid()) ;
			pstmt.setInt(9, user.getUid()) ;
			
			
			  pstmt.executeUpdate() ;
			
		} catch (SQLException e) {
			temp = false ;
			e.printStackTrace();
		}finally{
			try {
				pstmt.close() ;
				cn.close() ;
				databaseAccess.close() ;
			} catch (SQLException e) {
				e.printStackTrace();
				temp=false;
			}
	}
		return temp;
	}

	public User getUser(int uid) {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		User user = null;
		
		String sql = "select *  from user  where  uid=?";
		System.out.println(sql+"out");
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, uid);
			System.out.println(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				System.out.println("rs.next execute");
				user= new User();
				user.setUid(uid) ;
				user.setName(rs.getString("name")) ;
				user.setEmail(rs.getString("email")) ;
				user.setIdCard(rs.getString("idcard"));
				user.setGrade(rs.getString("grade"));
				user.setPassid(rs.getString("passid"));
				user.setPosition(rs.getString("position"));
				user.setCellPhone(rs.getString("cellphone"));
				user.setQQ(rs.getString("qq"));
				
			}
		
		}catch(Exception e){
			e.printStackTrace();
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return user;
	}

	public User getData(int uid,int pid){
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		User user=null;
		String sql="select information from orders where suid=? and spid=? ";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, uid) ;
			pstmt.setInt(2, pid) ;
			
			 rs=pstmt.executeQuery();
			if(rs.next()){
				user=JsonUtils.parse(rs.getString("information"), User.class);
			}
			
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}	
		}
		return user;
	}
	
	public SubProject getSubPro(int uid,int spid){
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		SubProject subpro=null;
 		String sql="select * from subproject where uid=? and spid=? ";
 		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, uid) ;
			pstmt.setInt(2, spid) ;
		    rs=pstmt.executeQuery();
			while(rs.next()){
				subpro=new SubProject();
				subpro.setName(rs.getString("name"));
				subpro.setStartTime(rs.getString("starttime"));
				subpro.setProjectinformation(rs.getString("description"));
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					
			}
		}
		return subpro;
 		
	}
	

public boolean delorder(int oid){
	DatabaseAccess databaseAccess = new DatabaseAccess(false);
	Connection cn = null;
	PreparedStatement pstmt=null;
	boolean ifdelete=false;
		String sql="update orders set ifdelete=1  where oid=?";
	try{
		cn = databaseAccess.getConn();
		pstmt = cn.prepareStatement(sql);
		pstmt.setInt(1, oid) ;
	    pstmt.execute();
	    ifdelete=true;
	}catch(Exception e){
		e.printStackTrace();
		System.out.println(e.getMessage());
		ifdelete = false ;
	}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
		try{
			pstmt.close();
			cn.close();
			databaseAccess.close() ;
		}catch(Exception ex){
				pstmt = null ;
				cn = null ;
				ifdelete = false ;
				
		}
	}
	return ifdelete;
}


public Pager<SubProject> getOrders(int uid,int pageNo) {
	DatabaseAccess databaseAccess = new DatabaseAccess(false);
	Connection cn = null;
	PreparedStatement pstmt=null;
	ResultSet rs = null;
	List<SubProject> list=new ArrayList<SubProject>();
	Pager<SubProject> page = new Pager<SubProject>() ;
	SubProject subpro=null;
		//String sql="select subproject.SPID,subproject.NAME,subproject.STARTTIME starttime,subproject.description , orders.osituation,orders.PRICE,orders.OID from orders INNER JOIN subproject ON orders.suid=? AND orders.SPID=subproject.SPID and orders.ifdelete=0 ORDER BY orders.OCREATTIME DESC";
	    String sql="select SPID, projectname, STARTTIME, description , is_success, orderprice,realprice, out_trade_no, OID from v_user_subproject_order where uid=? and orderdelete=0  order by OCREATTIME DESC";
	
	try{
		String fenyesql = " LIMIT " +Util.pageCount*(pageNo-1) + ","+Util.pageCount  ;
		cn = databaseAccess.getConn();
		pstmt = cn.prepareStatement(sql  + fenyesql);
		pstmt.setInt(1, uid) ;
		//pstmt.setInt(2, uid) ;
		System.out.println(sql  + fenyesql);
		rs = pstmt.executeQuery();
		int total = 0;
		while(rs.next()){
			subpro=new SubProject();
			subpro.setName(rs.getString("projectname"));
			subpro.setStartTime(rs.getString("STARTTIME"));
			subpro.setProjectinformation(rs.getString("description"));
			subpro.setSpid(rs.getInt("spid"));
			subpro.setOid(String.valueOf(rs.getInt("OID")));
			subpro.setOrderpay(  (rs.getString("is_success")==null?0:1)  );
			subpro.setOrderprice(rs.getString("orderprice"));
			subpro.setOut_trade_no(rs.getString("out_trade_no"));
			subpro.setRealprice(rs.getString("realprice"));//待修改
			System.out.println(rs.getString("orderprice")+"   orderprice");
			System.out.println(rs.getString("out_trade_no")+"  order_ID");
			//total++;
			list.add(subpro);
			 
		}
		sql="select count(*) from v_user_subproject_order where uid=? AND  orderdelete=0;";
		//sql="select count(*) from orders INNER JOIN subproject ON orders.suid=? AND orders.SPID=subproject.SPID and orders.ifdelete=0;";
		databaseAccess.setPreparedParameter(uid);//this.gettotalCount("" ,cn , rs , pstmt) ;
		total = databaseAccess.executeCount(sql);
		page.setTotal(total) ;
		page.setTotalPage(total) ;
		
		page.setDatas(list) ;
	}catch(Exception e){
		e.printStackTrace();
		System.out.println(e.getMessage());
	}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
		try{
			pstmt.close();
			cn.close();
			databaseAccess.close() ;
		}catch(Exception ex){
				pstmt = null ;
				cn = null ;
		}
	}
	return page ;
}

public int gettotalCount(String sql,Connection cn ,ResultSet rs ,PreparedStatement pstmt) {
	return gettotalCount(sql, "orders",cn , rs , pstmt );
}


}
